import { connection } from '../app/database/mysql';
import { goodsModel } from './kitap.model';
import { findFileById, getBolum } from '../house/house.service';
import {IMAGE_TYPE_GOODS} from '../app/constants';
import { geByIdtMovieCategory } from '../category/category.service';

/**
 * 获取内容
 */
export const getKitaps = async (
  pageNo: number,
  pageSize: number,
  type: number,
  status: number,
  recommend: number,
  is_free: number,
  video_source: number,
  book_form: number,
  keyWord: any,
  bolum_id: any,
) => {
  // 准备查询
  let statement = `
    SELECT SQL_CALC_FOUND_ROWS *, COUNT(id) OVER () AS total FROM kitap
  `;
  let where = []
  if (type >= 0) {
    where.push(`FIND_IN_SET(${type}, type)`)
  }
  if (status >= 0) {
    where.push(`status = ` + status)
  }
  if (recommend >= 0) {
    where.push(`recommend = ` + recommend)
  }
  if (is_free >= 0) {
    where.push(`is_free = ` + is_free)
  }
  if (video_source >= 0) {
    where.push(`video_source = ` + video_source)
  }
  if (book_form >= 0) {
    where.push(`book_form = ` + book_form)
  }
  if (keyWord != '') {
    where.push(`name LIKE '${keyWord}%' OR name_zh LIKE '${keyWord}%'`)
  }
  if (bolum_id >= 0) {
    where.push(`bolum_id = ` + bolum_id)
  }
  if (where.length > 0) {
    statement += ` where `
    for (let i = 0; i < where.length; i++) {
      statement += where[i]
      if (i < where.length - 1) {
        statement += ` and `
      }
    }
  }
  statement += ` ORDER by sortNum DESC, id DESC limit ` + (pageNo > 0 ? (pageNo - 1) * pageSize : 0) + `,` + pageSize
  
  // 执行查询
  const [data] = await connection.promise().query(statement);

  let totalCount: any;
  if (data[0] && data[0].total) totalCount = data[0].total;
  let totalPage = (totalCount % pageSize) > 0 ? parseInt(`${totalCount / pageSize}`) + 1 : totalCount / pageSize;

  for (let item of data as any) {
    let categoryNumbers = [];
    if (item.type && typeof item.type === 'string') {  // 添加类型检查
      const matches = item.type.match(/\d+(\.\d+)?/g);
      if (matches) {  // 检查是否有匹配结果
        categoryNumbers = matches.map(Number);
      }
    }
    item.type = categoryNumbers;
  }

  for (let i of data as any) {
    
    if (i.type && i.type.length > 0) {
      let category_arr = [];
      for (let e of i.type) {
        let category_dan = await geByIdtMovieCategory(e)
        category_arr.push(category_dan);
      }
      i.type = category_arr;
    }

    if (i.id && i.id > 0) {
      let {multiset_count} = await computeSet(i.id)
      i.multiset_count = multiset_count;

      let { like_count } = await likeTotal(i.id); // 统计收藏
      i.likeTotal = like_count;

      let { comment_count } = await commentTotal(i.id); // 统计评论
      i.commentTotal = comment_count;
    }
    
    // 书籍类型 【音频，文字】
    if (i.book_form && i.book_form > 0) {
      i.book_form_text = i.book_form == 1 ? 'ئاۋازلىق' : i.book_form == 2 ? 'خەتلىك' : i.book_form == 3 ? 'خەتلىك ھەم ئاۋازلىق' : '';
    }
  }


  let res = {
    data,
    totalPage,
    totalCount
  }
  // 提供数据
  return res;
}

/**
 * 获取内容
 */
export const getRecommend = async () => {
  // 准备查询
  let statement = `
    SELECT * FROM kitap WHERE status = 1 ORDER BY RAND() LIMIT 4
  `;
  
  // 执行查询
  const [data] = await connection.promise().query(statement);

  for (let i of data as any) {

    if (i.id && i.id > 0) {
      let { multiset_count } = await computeSet(i.id)
      i.multiset_count = multiset_count;
    }

  }
 
  // 提供数据
  return data;
}

/**
 * 计算上传集数
 */
export const computeSet = async (id: number) => {
  // 准备查询
  const statement = `
    SELECT COUNT(*) AS multiset_count FROM audioBooks WHERE book_id = ? AND status = 1;
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, id);

  // 提供数据
  return data[0];
}

/**
 * 计算类目数量
 */
export const computeCategory = async (id: number) => {
  // 准备查询
  const statement = `
    SELECT COUNT(*) AS category_count 
    FROM kitap 
    WHERE CONCAT(',', type, ',') LIKE '%,${id},%'
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, id);

  // 提供数据
  return data[0];
}


/**
 * 按 ID 获取内容
 */
export const getIdKitap = async (postId: number) => {
  // 准备查询
  const statement = `
    SELECT * FROM kitap
    WHERE id = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, postId);

  
  data[0].large = await getLargeSet(data[0].bolum_id);
  data[0].bolumArr = await getBolum(postId);

  // 提供数据
  return data[0];
}


/**
 * 按关键字获取（模糊查询）
 */
export const byKeyWordSelect = async (keyWord: any) => {
  // 准备查询
  const statement = `
    SELECT * FROM kitap
    WHERE (name LIKE '%${keyWord}%')
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, keyWord);

  // 提供数据
  return data;
}

/**
 * 创建内容
 */
export const createKitap = async (post: goodsModel) => {
  // 准备查询
  const statement = `
    INSERT INTO kitap
    SET ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, post);

  // 提供数据
  return data;
}

/**
 * 创建作者
 */
export const createAuthorCon = async (post: goodsModel) => {
  // 准备查询
  const statement = `
    INSERT INTO author_con
    SET ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, post);

  // 提供数据
  return data;
}

/**
 * 更新内容
 */
export const updateKitap = async (post: goodsModel, postId: number) => {
  // 准备查询
  const statement = `
    UPDATE kitap
    SET ?
    WHERE id = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, [post, postId]);

  // 提供数据
  return data;
}

/**
 * 删除内容
 */
export const deleteKitap = async (postId: number) => {
  // 准备查询
  const statement = `
    DELETE FROM kitap
    WHERE id = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, postId);

  // 提供数据
  return data;
}

/**
 * 删除内容
 */
export const deleteKisim = async (postId: number) => {
  // 准备查询
  const statement = `
    DELETE FROM audioBooks
    WHERE id = ?
  `;;

  // 执行查询
  const [data] = await connection.promise().query(statement, postId);

  // 提供数据
  return data;
}

/**
 * 状态
 */
export const kitapStatusChange = async (postId: number, post: any) => {
  // 准备数据
  const statement = `
    UPDATE kitap
      SET ?
      WHERE id = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, [post, postId]);

  // 提供数据
  return data;
};

/**
 * 获取音频;
 */
export const getAudios = async (bookId: number, pageSize: number, pageNo: number, status: number, bolum_id: number) => {
  // 准备查询
  let statement = `
    SELECT SQL_CALC_FOUND_ROWS *, COUNT(id) OVER () AS total FROM audioBooks
  `;
  let where = []
  if (bookId >= 0) {
    where.push(`book_id = ` + bookId)
  }
  if (status >= 0) {
    where.push(`status = ` + status)
  }
  if (bolum_id > 0) {
    where.push(`bolum_id = ` + bolum_id)
  }
  if (where.length > 0) {
    statement += ` where `
    for (let i = 0; i < where.length; i++) {
      statement += where[i]
      if (i < where.length - 1) {
        statement += ` and `
      }
    }
  }
  statement += ` ORDER by id ASC, id DESC limit ` + (pageNo > 0 ? (pageNo - 1) * pageSize : 0) + `,` + pageSize;

  // 执行查询
  const [data] = await connection.promise().query(statement);

  let totalCount: any;
  if (data[0] && data[0].total) totalCount = data[0].total;
  let totalPage = (totalCount % pageSize) > 0 ? parseInt(`${totalCount / pageSize}`) + 1 : totalCount / pageSize;

  for (let item of data as any) {
    let name = await getIdKitap(item.book_id);
    item.name = name.name;
  }

  let res = {
    data,
    totalPage,
    totalCount
  }
  // 提供数据
  return res;;
}

/**
 * 统计收藏
 */
const likeTotal = async (bookId: number) => {
  // 准备查询
  const statement = `
    SELECT COUNT(*) AS like_count FROM favorites WHERE book_id = ?;
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, bookId);

  // 提供数据
  return data[0];
}

/**
 * 统计评论
 */
const commentTotal = async (bookId: number) => {
  // 准备查询
  const statement = `
    SELECT COUNT(*) AS comment_count FROM comment WHERE book_id = ?;
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, bookId);

  // 提供数据
  return data[0];
}

/**
 * 统计评论
 */
export const countBook = async () => {
  // 准备查询
  const statement = `
    SELECT COUNT(*) AS count_book FROM kitap;
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement);

  // 提供数据
  return data[0];
}

/**
 * بۆلۈم قوشۇش
 */
export const createBolum = async (post: any) => {
  // 准备查询
  const statement = `
    INSERT INTO in_book_bolum
    SET ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, post);

  // 提供数据
  return data;
}

/**
 * بۆلۈمگە ئېرىشىش
 */
const getLargeSet = async (bolum_id: number) => {
  // 准备查询
  const statement = `
    SELECT * FROM largeSet
    WHERE bolum_id = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, bolum_id);

  // 提供数据
  return data;
}

/**
 * بولۇم نۇمۇرى بلەن ئەسەرگە ئېرىشىش
 */
export const getByIdBolum = async (bolum_id: number, book_id: number) => {
  // 准备查询
  const statement = `
    SELECT * FROM audioBooks
    WHERE bolum_id = ? AND book_id = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, [bolum_id, book_id]);

  for (let item of data as any) {
    let name = await getIdKitap(item.book_id);
    item.name = name.name;
  }

  // 提供数据
  return data;
}

/**
 * بۆلۈمگە ئېرىشىش كىتاب ئايدى دىن
 */
export const byBookIdGetBolum = async (post: any) => {
  // 准备查询
  const statement = `
    SELECT * FROM in_book_bolum
    WHERE book_id = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, post);

  // 提供数据
  return data;
}
